﻿using BusinessLayer;
using BusinessLayer.VDataEntities;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TrialManagementTool.Helper;
using TrialManagmentTool.Models;

namespace TrialManagementTool.Controllers
{
    [Authentication]
    public class TrialExportController : Controller
    {
        //
        // GET: /TrialExport/
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ExportTrialData()
        {
            //LogHelper.Info("ExportTrialData time:" + DateTime.Now);
            //准备文件
            if (!Directory.Exists(Server.MapPath("~/App_Data/uploads")))
            {
                Directory.CreateDirectory(Server.MapPath("~/App_Data/uploads"));
            }
            //删除目录下其他生成的文件
            DeleteFiles(Server.MapPath("~/App_Data/uploads"));

            string sourceFile = Server.MapPath("~/Helper/template.xlsx");
            string destinationFile = string.Format("{0}{1}.xlsx", Server.MapPath("~/App_Data/uploads/"), DateTime.Now.ToString("yyyyMMddHHmmss"));
            System.IO.File.Copy(sourceFile, destinationFile);

            //需要赋值的数据
            List<VReport> observeList = new List<VReport>();
            List<VReport> nonObserveList = new List<VReport>();
            VTrialSite trialSite = new VTrialSite();
            VTrialDetail trial = new VTrialDetail();
            List<TrialExportModel> trialExportList = new List<TrialExportModel>();

            //List<VDataEntity> productCategoryList = ProductBusiness.GetProductCategoryList();
            //foreach (VDataEntity productCategoryitem in productCategoryList)
            //{
            int rowIndex1 = 2;//除草剂
            int rowIndex2 = 2;//杀菌剂
            int rowIndex3 = 2;//杀虫剂
            int rowIndex4 = 2;//肥料增效剂

            List<IGrouping<string, VReport>> allTrialData = TrialBusiness.GetAllTrialDataByProductStataus();

            foreach (var allTrialByProductCategoryByPromoter in allTrialData)
            {
                long TrialPromoterIndex = 1;
                foreach (var item in allTrialByProductCategoryByPromoter)
                {
                    //关于trial的信息，同一试验不同处理使用相同的信息
                    trialSite = TrialBusiness.GetTrialSiteDetail(item.TrialSiteID);
                    trial = TrialBusiness.GetTrialDetail(trialSite.ID);
                    List<VReport> reports = trial.Reports.ToList();

                    //处理试验点没有报告的case
                    if (reports.Count() == 0)
                    {
                        TrialExportModel trialExportModel = new TrialExportModel();
                        trialExportModel.PromoterTrialIndex = TrialPromoterIndex;

                        //关于报告的信息
                        List<VReport> nonObserveInfoDataList = new List<VReport>();
                        List<VReport> observeInfoDataList = new List<VReport>();

                        //一次处理包含用药信息，观察信息，基本信息，序号等
                        //trialExportModel.rowIndex = rowIndex;
                        trialExportModel.trial = trial;
                        trialExportModel.trialSite = trialSite;
                        trialExportModel.yongyaoList = nonObserveInfoDataList;
                        trialExportModel.guanchaList = observeInfoDataList;
                        //rowIndex++;
                        //往Excel写入内容
                        switch (trial.ProductCategory.Name)
                        {
                            case "除草剂":
                                trialExportModel.productCategory = "除草剂";
                                trialExportModel.sheetPage = 0;
                                trialExportModel.rowIndex = rowIndex1;
                                rowIndex1++;
                                break;
                            case "杀菌剂":
                                trialExportModel.productCategory = "杀菌剂";
                                trialExportModel.sheetPage = 1;
                                trialExportModel.rowIndex = rowIndex2;
                                rowIndex2++;
                                break;
                            case "杀虫剂":
                                trialExportModel.productCategory = "杀虫剂";
                                trialExportModel.sheetPage = 2;
                                trialExportModel.rowIndex = rowIndex3;
                                rowIndex3++;
                                break;
                            case "肥料增效剂":
                                trialExportModel.productCategory = "肥料增效剂";
                                trialExportModel.sheetPage = 3;
                                trialExportModel.rowIndex = rowIndex4;
                                rowIndex4++;
                                break;
                            default:
                                break;
                        }
                        trialExportList.Add(trialExportModel);
                    }

                    for (int j = 0; j < reports.Count(); j++)
                    {

                        //关于报告的信息
                        TrialExportModel trialExportModel = new TrialExportModel();
                        List<VReport> nonObserveInfoDataList = new List<VReport>();
                        List<VReport> observeInfoDataList = new List<VReport>();

                        ProcessViewModel perProcessItem = new ProcessViewModel();
                        perProcessItem.NonObserveDataList = new List<VReport>();
                        perProcessItem.ObserveDataList = new List<VReport>();
                        trialExportModel.reportName = reports[j].Name;
                        trialExportModel.PromoterTrialIndex = TrialPromoterIndex;
                        perProcessItem.reportName = reports[j].Name;
                        List<VReportInfo> reportInfoYongyao = ReportBusiness.GetProcessInfoByProcessID(reports[j].ID, false);
                        for (int i = 0; i < reportInfoYongyao.Count(); i++)
                        {
                            //用药信息最多3次
                            if (i < 3)
                            {
                                VReport vReportitem = ReportBusiness.GetByID(reportInfoYongyao[i].ID);
                                vReportitem.headTitle = string.Format("第{0}次用药", Common.ConvertNumToChinese(i + 1));
                                perProcessItem.NonObserveDataList.Add(vReportitem);
                                nonObserveInfoDataList.Add(vReportitem);
                            }
                        }
                        List<VReportInfo> reportInfoGuancha = ReportBusiness.GetProcessInfoByProcessID(reports[j].ID, true);
                        for (int i = 0; i < reportInfoGuancha.Count(); i++)
                        {
                            //观察最多5次
                            if (i < 5)
                            {
                                VReport vReportitem = ReportBusiness.GetByID(reportInfoGuancha[i].ID);
                                vReportitem.headTitle = string.Format("第{0}次调查", Common.ConvertNumToChinese(i + 1));
                                perProcessItem.ObserveDataList.Add(vReportitem);
                                observeInfoDataList.Add(vReportitem);
                            }
                        }
                        //一次处理包含用药信息，观察信息，基本信息，序号等
                        //trialExportModel.rowIndex = rowIndex;
                        trialExportModel.trial = trial;
                        trialExportModel.trialSite = trialSite;
                        trialExportModel.yongyaoList = nonObserveInfoDataList;
                        trialExportModel.guanchaList = observeInfoDataList;
                        //rowIndex++;
                        //往Excel写入内容
                        switch (trial.ProductCategory.Name)
                        {
                            case "除草剂":
                                trialExportModel.productCategory = "除草剂";
                                trialExportModel.sheetPage = 0;
                                trialExportModel.rowIndex = rowIndex1;
                                rowIndex1++;
                                break;
                            case "杀菌剂":
                                trialExportModel.productCategory = "杀菌剂";
                                trialExportModel.sheetPage = 1;
                                trialExportModel.rowIndex = rowIndex2;
                                rowIndex2++;
                                break;
                            case "杀虫剂":
                                trialExportModel.productCategory = "杀虫剂";
                                trialExportModel.sheetPage = 2;
                                trialExportModel.rowIndex = rowIndex3;
                                rowIndex3++;
                                break;
                            case "肥料增效剂":
                                trialExportModel.productCategory = "肥料增效剂";
                                trialExportModel.sheetPage = 3;
                                trialExportModel.rowIndex = rowIndex4;
                                rowIndex4++;
                                break;
                            default:
                                break;
                        }
                        trialExportList.Add(trialExportModel);
                    }
                    TrialPromoterIndex++;
                }
            }

            //}
            ProcessExcelFile(destinationFile, trialExportList.OrderBy(c=>c.trialSite.CreateDate.Value).ToList());
            //LogHelper.Info("ExportTrialData end time:" + DateTime.Now);
            return File(destinationFile, "application/ms-excel", string.Format("Trial-{0}-试验数据.xlsx", DateTime.Now.ToString("yyyyMMdd")));
        }

        /// <summary>
        /// 使用第三方控件往Excel内容
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="exportData"></param>
        public void ProcessExcelFile(string filePath, List<TrialExportModel> exportData)
        {
            Dictionary<long, List<VItem>> dict = ReportBusiness.GetAllFieldsDataSource();
            XSSFWorkbook wk = null;
            int rowIndex1 = 2, rowIndex2 = 2, rowIndex3 = 2, rowIndex4 = 2;
            int rowNum = 0;
            using (FileStream fs = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //把xls文件读入workbook变量里，之后就可以关闭了  
                wk = new XSSFWorkbook(fs);
                fs.Close();
            }
            for (int i = 0; i < exportData.Count; i++)
            {
                ISheet sheet = wk.GetSheetAt(exportData[i].sheetPage);
                //在第二行创建行    
                switch (exportData[i].sheetPage)
                {
                    case 0:
                        rowNum = rowIndex1;
                        rowIndex1++;
                        break;
                    case 1:
                        rowNum = rowIndex2;
                        rowIndex2++;
                        break;
                    case 2:
                        rowNum = rowIndex3;
                        rowIndex3++;
                        break;
                    case 3:
                        rowNum = rowIndex4;
                        rowIndex4++;
                        break;
                    default:
                        break;
                }
                IRow row = sheet.GetRow(rowNum);
                if (row == null)
                {
                    row = sheet.CreateRow(rowNum);
                }
                //cell 开始序号为1（第二个单元格）
                //基本信息,试验编号在用药信息处赋值
                Setvalue(row, 0, string.Format("{0}-{1}-{2}-{3}-{4}-{5}-{6}-{7}", exportData[i].trial.Product, exportData[i].trial.CreatedDate.Value.Year, exportData[i].trial.CreatedDate.Value.Month.ToString().PadLeft(2, '0'), exportData[i].trial.Crop,
                  exportData[i].trialSite.Object, exportData[i].trial.Province, exportData[i].trial.Promoter, exportData[i].PromoterTrialIndex.ToString().PadLeft(3, '0')));//试验编号
                Setvalue(row, 1, exportData[i].reportName);//CA
                Setvalue(row, 2, exportData[i].trial.TTM == null ? "" : exportData[i].trial.TTM.Name);//TTM
                Setvalue(row, 3, exportData[i].trial.ProjectCategory == null ? "" : exportData[i].trial.ProjectCategory.Name);//项目类别
                Setvalue(row, 4, exportData[i].trial.CA);//CA
                Setvalue(row, 5, exportData[i].trial.FSM);//FSM
                Setvalue(row, 6, exportData[i].trial.Product);//产品
                Setvalue(row, 7, exportData[i].trial.Crop);//农作物
                Setvalue(row, 8, exportData[i].trial.Promoter);//试验员
                Setvalue(row, 9, exportData[i].trial.Province);//省份

                int trialSiteIndex = 10;
                switch (exportData[i].productCategory)
                {
                    case "除草剂"://除草剂
                        Setvalue(row, trialSiteIndex, exportData[i].trialSite.Address);//市县/乡镇
                        Setvalue(row, trialSiteIndex + 1, exportData[i].trialSite.Partner);//合作单位
                        Setvalue(row, trialSiteIndex + 2, exportData[i].trialSite.CropCategory);//作物品种
                        Setvalue(row, trialSiteIndex + 3, exportData[i].trialSite.SeedMode);//栽培模式
                        Setvalue(row, trialSiteIndex + 4, exportData[i].trialSite.Object);//靶标杂草／病害／害虫
                        string isclose = "否";
                        if (exportData[i].trialSite.IsClosed.HasValue)
                        {
                            if (exportData[i].trialSite.IsClosed.Value == true)
                            {
                                isclose = "是";
                            }
                        }
                        Setvalue(row, trialSiteIndex + 5, isclose);//是否封闭
                        Setvalue(row, trialSiteIndex + 6, exportData[i].trialSite.SubCropCategory);//季节
                        break;
                    case "杀菌剂"://杀菌剂
                        Setvalue(row, trialSiteIndex, exportData[i].trialSite.Address);//市县/乡镇
                        Setvalue(row, trialSiteIndex + 1, exportData[i].trialSite.Partner);//合作单位
                        Setvalue(row, trialSiteIndex + 2, exportData[i].trialSite.CropCategory);//作物品种
                        Setvalue(row, trialSiteIndex + 3, exportData[i].trialSite.SeedMode);//栽培模式
                        Setvalue(row, trialSiteIndex + 4, exportData[i].trialSite.Object);//靶标杂草／病害／害虫
                        Setvalue(row, trialSiteIndex + 5, exportData[i].trialSite.SubCropCategory);//季节
                        break;
                    case "杀虫剂"://杀虫剂
                        Setvalue(row, trialSiteIndex, exportData[i].trialSite.Address);//市县/乡镇
                        Setvalue(row, trialSiteIndex + 1, exportData[i].trialSite.Partner);//合作单位
                        Setvalue(row, trialSiteIndex + 2, exportData[i].trialSite.CropCategory);//作物品种
                        Setvalue(row, trialSiteIndex + 3, exportData[i].trialSite.SeedMode);//栽培模式
                        Setvalue(row, trialSiteIndex + 4, exportData[i].trialSite.Object);//靶标杂草／病害／害虫
                        Setvalue(row, trialSiteIndex + 5, exportData[i].trialSite.SubCropCategory);//季节
                        break;
                    case "肥料增效剂"://肥料增效剂
                        Setvalue(row, trialSiteIndex, exportData[i].trialSite.Address);//市县/乡镇
                        Setvalue(row, trialSiteIndex + 1, exportData[i].trialSite.Partner);//合作单位
                        Setvalue(row, trialSiteIndex + 2, exportData[i].trialSite.CropCategory);//作物品种
                        Setvalue(row, trialSiteIndex + 3, exportData[i].trialSite.SeedMode);//栽培模式
                        Setvalue(row, trialSiteIndex + 4, exportData[i].trialSite.Object);//靶标杂草／病害／害虫
                        Setvalue(row, trialSiteIndex + 5, exportData[i].trialSite.SubCropCategory);//季节
                        break;
                }
                //赋值
                //循环用药信息
                int yongyaoBeginIndex = 10;
                switch (exportData[i].productCategory)
                {
                    case "除草剂"://除草剂
                        yongyaoBeginIndex = 17;
                        break;
                    case "杀菌剂"://杀菌剂
                        yongyaoBeginIndex = 16;
                        break;
                    case "杀虫剂"://杀虫剂
                        yongyaoBeginIndex = 16;
                        break;
                    case "肥料增效剂"://肥料增效剂
                        yongyaoBeginIndex = 16;
                        break;
                    default:
                        break;
                }


                for (int j = 0; j < exportData[i].yongyaoList.Count; j++)
                {
                    for (int k = 0; k < exportData[i].yongyaoList[j].inputFields.ToList().Count; k++)
                    {
                        VInputData currentItem = exportData[i].yongyaoList[j].inputFields.ToList()[k];
                        SetValueByType(dict, row, yongyaoBeginIndex, currentItem);
                        yongyaoBeginIndex++;
                    }
                }


                //循环观察信息
                int guanchaBeginIndex = 40;
                switch (exportData[i].productCategory)
                {
                    case "除草剂"://除草剂
                        guanchaBeginIndex = 62;
                        break;
                    case "杀菌剂"://杀菌剂
                        guanchaBeginIndex = 61;
                        break;
                    case "杀虫剂"://杀虫剂
                        guanchaBeginIndex = 61;
                        break;
                    case "肥料增效剂"://肥料增效剂
                        guanchaBeginIndex = 58;
                        break;
                    default:
                        break;
                }

                for (int j = 0; j < exportData[i].guanchaList.Count; j++)
                {
                    for (int k = 0; k < exportData[i].guanchaList[j].inputFields.ToList().Count; k++)
                    {
                        VInputData currentItem = exportData[i].guanchaList[j].inputFields.ToList()[k];
                        SetValueByType(dict, row, guanchaBeginIndex, currentItem);
                        guanchaBeginIndex++;
                    }
                }

                //总结报告
                int FinalBeginIndex = 80;
                string isAccept = "";
                if (exportData[i].trialSite.IsAcceptable.HasValue)
                {
                    if (exportData[i].trialSite.IsAcceptable.Value == true)
                    {
                        isAccept = "是";
                    }
                    else
                    {
                        isAccept = "否";
                    }
                }
                string effect = "";
                switch (exportData[i].trialSite.EffectResult)
                {
                    case "1":
                        effect = "不能接受";
                        break;
                    case "2":
                        effect = "有效";
                        break;
                    case "3":
                        effect = "效果好";
                        break;
                    case "4":
                        effect = "效果优异";
                        break;
                    default:
                        effect = "";
                        break;
                }
                switch (exportData[i].productCategory)
                {
                    case "除草剂"://除草剂
                        FinalBeginIndex = 117;
                        break;
                    case "杀菌剂"://杀菌剂
                        FinalBeginIndex = 116;
                        break;
                    case "杀虫剂"://杀虫剂
                        FinalBeginIndex = 116;
                        break;
                    case "肥料增效剂"://肥料增效剂
                        FinalBeginIndex = 113;
                        break;
                    default:
                        break;
                }
                Setvalue(row, FinalBeginIndex, exportData[i].trialSite.LastObserveDate.HasValue ? exportData[i].trialSite.LastObserveDate.Value.ToString("yyyy-MM-dd HH:mm") : "");//最后一次调查时间
                Setvalue(row, FinalBeginIndex + 1, isAccept);//对此药效农户是否接受
                Setvalue(row, FinalBeginIndex + 2, effect);//最后一次调查目测药效
                Setvalue(row, FinalBeginIndex + 3, ConvertString(exportData[i].trialSite.EffectDescription));//具体药效描述（动态过程）
                Setvalue(row, FinalBeginIndex + 4, ConvertString(exportData[i].trialSite.CropDescription));//其它说明
                Setvalue(row, FinalBeginIndex + 5, ConvertString(exportData[i].trialSite.YieldEffect));//对产量影响%(减产用负值表示）
                Setvalue(row, FinalBeginIndex + 6, ConvertString(exportData[i].trialSite.Comments));//备注
            }

            using (FileStream fileStream = System.IO.File.Open(filePath, FileMode.Create, FileAccess.ReadWrite))
            {
                wk.Write(fileStream);
                fileStream.Close();
            }
        }

        /// <summary>
        /// 填充Trial Report 内容
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <param name="currentItem"></param>
        private void SetValueByType(Dictionary<long, List<VItem>> dict, IRow row, int index, VInputData currentItem)
        {
            switch (currentItem.DataType)
            {
                case 1://文字
                case 2://数字
                case 3://时间
                    Setvalue(row, index, currentItem.Value);
                    break;
                case 4://下拉框
                    string value = "";
                    if (!string.IsNullOrEmpty(currentItem.Value))
                    {
                        VItem item = dict[currentItem.FieldID].Find(c => c.Value == currentItem.Value);
                        if (item != null)
                        {
                            value = item.Description;
                        }
                    }
                    Setvalue(row, index, value);
                    break;
                case 5://checkbox
                    string status = "";
                    if (currentItem.Value == "true")
                    {
                        status = "是";
                    }
                    else if (currentItem.Value == "false")
                    {
                        status = "否";
                    }
                    Setvalue(row, index, status);
                    break;
            }
        }
        /// <summary>
        /// 填值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="cellIndex"></param>
        /// <param name="value"></param>
        public void Setvalue(IRow row, int cellIndex, string value)
        {
            try
            {
                row.CreateCell(cellIndex).SetCellValue(value);
            }
            catch (Exception ex)
            {
                LogHelper.Error("Excel Insert value fail", ex);
            }
        }

        /// <summary>
        /// 删除之前生成的Excel 文件
        /// </summary>
        /// <param name="path"></param>
        public void DeleteFiles(string path)
        {
            if (Directory.Exists(path) == false)
            {
                return;
            }
            DirectoryInfo dir = new DirectoryInfo(path);
            FileInfo[] files = dir.GetFiles();

            foreach (var item in files)
            {
                try
                {
                    System.IO.File.Delete(item.FullName);
                }
                catch (Exception ex)
                {
                    LogHelper.Error("Delete File fail:", ex);
                    continue;
                }
            }
        }

        public static string ConvertString(string content)
        {
            if (string.IsNullOrEmpty(content))
            {
                return "";
            }
            else
            {
                return content.Replace("<br/>", Environment.NewLine);
            }
        }
    }

}

